﻿Imports System.Data.OleDb
Imports DTO
Public Class PhieuTraSachDAO
    Public Function LayTongNoTheoMaDocGia(ByVal maDocGia As Integer) As Integer
        DataProvider.OpenConnecttion()
        Dim sql As String = " SELECT PHIEUTRASACH.TongNo, PHIEUTRASACH.NgayTra, PHIEUTRASACH.MaDocGia "
        sql = sql & " FROM(PHIEUTRASACH) WHERE (((PHIEUTRASACH.NgayTra)=(select Max([PHIEUTRASACH].[NgayTra])  "
        sql = sql & " from [PHIEUTRASACH] where [PHIEUTRASACH].[MaDocGia] = " & maDocGia.ToString() & ")) AND ((PHIEUTRASACH.MaDocGia)=" & maDocGia.ToString() & "));"

        Dim reader As OleDbDataReader
        reader = DataProvider.ExecSelect(sql)
        reader.Read()

        Return reader.GetInt32(0)
    End Function

    Public Function ThemPhieuTra(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function

    Public Function LayDSPhieuTra() As List(Of PhieuTraSachDTO)
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader
        Dim sql As String = "select MaPhieuTraSach,MaDocGia,NgayTra,TienPhatKyNay,TienNo,TongNo,MaChiTietPhieuTraSach "
        sql = sql & "from PHIEUTRASACH"

        reader = DataProvider.ExecSelect(sql)

        Dim dsPTS As New List(Of PhieuTraSachDTO)
        While reader.Read()
            Dim pts As New PhieuTraSachDTO()
            pts.MaPhieu = reader.GetInt32(0)
            pts.MaDocGia = reader.GetInt32(1)
            pts.NgayTra = reader.GetDateTime(2)
            pts.TienPhatKyNay = reader.GetInt32(3)
            pts.TienNo = reader.GetInt32(4)
            pts.TongNo = reader.GetInt32(5)
            pts.MaChiTietPhieu = reader.GetInt32(6)
            dsPTS.Add(pts)
        End While
        DataProvider.CloseConnect()
        Return dsPTS
    End Function
End Class
